﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Models;
using DBUnitility;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    public class RemoteIOService
    {
        /// <summary>
        /// 获取所有配置好的远程IO
        /// </summary>
        /// <returns></returns>
        public List<ExCall> GetAllRemoteIO()
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("select RemoteID,IP,RemoteName,Port,RStationOne,RStationTwo,RStationThreed,RemoteIO.TrackId,TrackName,PortName from RemoteIO ");
            stringBuilder.Append("inner join TrackInfo on TrackInfo.TrackId = RemoteIO.TrackId ");
            string sql = stringBuilder.ToString();
            List<ExCall> list = new List<ExCall>();
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                while (objReader.Read())
                {
                    list.Add ( new ExCall()
                    {
                        RemoteID = Convert.ToInt32(objReader["RemoteID"]),
                        IP = objReader["IP"].ToString(),
                        RemoteName = objReader["RemoteName"].ToString(),
                        Port = Convert.ToInt32(objReader["Port"]),
                        PortName = objReader["PortName"].ToString(),
                        Station1 = Convert.ToInt32(objReader["RStationOne"]),
                        Station2 = Convert.ToInt32(objReader["RStationTwo"]),
                        Station3 = Convert.ToInt32(objReader["RStationThreed"]),
                        TrackId = Convert.ToInt32(objReader["TrackId"]),
                        TrackName = objReader["TrackName"].ToString(),
                    });
                }
                objReader.Close();
                return list;
            }
            catch (Exception)
            {
                throw new Exception("获取远程IO数据失败，详细信息：");
            }
        }
        public List<ExCall> GetRemoteIO()
        {
            string sql = "select distinct IP,RemoteName from RemoteIO";
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<ExCall> list = new List<ExCall>();
            while (objReader.Read())
            {
                list.Add(new ExCall() 
                {
                    IP = objReader["IP"].ToString(),
                    RemoteName = objReader["RemoteName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        #region 根据IP地址和端口号获取详细呼叫信息
        /// <summary>
        /// 根据IP地址和端口号获取详细呼叫信息
        /// </summary>
        /// <param name="ip">远程IO的IP地址</param>
        /// <param name="port">远程IO的端口</param>
        /// <returns>返回对于远程IO的呼叫详细信息</returns>
        public ExCall GetRemoteIOByIP(string ip, int port)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.Append("select RemoteID,IP,RemoteName,Port,RStationOne,RStationTwo,RStationThreed,RemoteIO.TrackId,TrackName,PortName from RemoteIO ");
            stringBuilder.Append("inner join TrackInfo on TrackInfo.TrackId = RemoteIO.TrackId ");
            stringBuilder.Append("where IP ='{0}' and Port = {1}");
            string sql = String.Format(stringBuilder.ToString(), ip, port.ToString());
            ExCall objExCall = null;
            try
            {
                SqlDataReader objReader = SQLHelper.GetReader(sql);
                while (objReader.Read())
                {
                    objExCall = new ExCall()
                    {
                        RemoteID = Convert.ToInt32(objReader["RemoteID"]),
                        IP = objReader["IP"].ToString(),
                        RemoteName = objReader["RemoteName"].ToString(),
                        Port = Convert.ToInt32(objReader["Port"]),
                        PortName = objReader["PortName"].ToString(),
                        Station1 = Convert.ToInt32(objReader["RStationOne"]),
                        Station2 = Convert.ToInt32(objReader["RStationTwo"]),
                        Station3 = Convert.ToInt32(objReader["RStationThreed"]),
                        TrackId = Convert.ToInt32(objReader["TrackId"]),
                        TrackName = objReader["TrackName"].ToString(),
                        StartTime = DateTime.Now
                    };                   
                }
                objReader.Close();
                return objExCall;
            }
            catch (Exception)
            {               
                throw new Exception("获取远程IO数据失败，详细信息：");
            }
        }
        #endregion

        /// <summary>
        /// 根据IP和端口号判断是否已存在该数据
        /// </summary>
        /// <param name="ip">IP地址</param>
        /// <param name="port">远程IO端口</param>
        /// <returns>结果</returns>
        public int IsExistsIP(string ip, string port)
        {
            string sql = "select count(*) from RemoteIO where IP = '{0}' and Port ={1}";
            sql = string.Format(sql,ip,port);
            int result = Convert.ToInt32(SQLHelper.GetSingalResult(sql));
            return result;
        }
        /// <summary>
        /// 增加远程IO
        /// </summary>
        /// <param name="objExCall"></param>
        /// <returns></returns>
        public int AddRemoteIO(ExCall objExCall)
        {
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@IP",objExCall.IP),
                new SqlParameter("@RemoteName",objExCall.RemoteName),
                new SqlParameter("@Port",objExCall.Port),
                new SqlParameter("@PortName",objExCall.PortName),
                new SqlParameter("@RStationOne",objExCall.Station1),
                new SqlParameter("@RStationTwo",objExCall.Station2),
                new SqlParameter("@RStationThreed",objExCall.Station3),
                new SqlParameter("@TrackId",objExCall.TrackId),
            };
            int result = Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_AddRemoteIO", param));
            return result;
        }
        /// <summary>
        /// 删除远程IO
        /// </summary>
        /// <param name="remoteId"></param>
        /// <returns></returns>
        public int DeleteRemoteIO(string remoteId)
        {
            return Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_DeleteRemoteIO",new SqlParameter[] { new SqlParameter("@RemoteID",remoteId)}));
        }
        /// <summary>
        /// 修改远程IO
        /// </summary>
        /// <param name="objExCall"></param>
        /// <returns></returns>
        public int UpdateRemoteIO(ExCall objExCall)
        {
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@IP",objExCall.IP),
                new SqlParameter("@RemoteName",objExCall.RemoteName),
                new SqlParameter("@Port",objExCall.Port),
                new SqlParameter("@PortName",objExCall.PortName),
                new SqlParameter("@RStationOne",objExCall.Station1),
                new SqlParameter("@RStationTwo",objExCall.Station2),
                new SqlParameter("@RStationThreed",objExCall.Station3),
                new SqlParameter("@TrackId",objExCall.TrackId),
                new SqlParameter("@RemoteID",objExCall.RemoteID)
            };
            int result = Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_ModifyRemoteIO", param));
            return result;
        }
        public List<ExCall> GetUnfinishCallByTime(DateTime dateTime)
        {
            /*从起始日期查询，小于等于2年（数据库存储过程代码<=2）*/
            SqlDataReader objReader = SQLHelper.GetReaderByProcedure("usp_UnfinishCallByTime",new SqlParameter[]{new SqlParameter("@StartTime",dateTime)});
            List<ExCall> list = new List<ExCall>();
            while (objReader.Read())
            {
                list.Add(new ExCall() 
                {
                    IP = objReader["IP"].ToString(),
                    RemoteName = objReader["RemoteName"].ToString(),
                    PortName = objReader["PortName"].ToString(),
                    Port = Convert.ToInt32(objReader["Port"]),
                    StartTime = Convert.ToDateTime(objReader["StartTime"]),
                    Station1 = Convert.ToInt32(objReader["RStationOne"]),
                    Station2= Convert.ToInt32(objReader["RStationTwo"]),
                    Station3 = Convert.ToInt32(objReader["RStationThreed"]),
                    TrackId = Convert.ToInt32(objReader["TrackId"]),
                    TrackName = objReader["TrackName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        public int AddUnfinishCall(ExCall objExCall)
        {
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@IP",objExCall.IP),
                new SqlParameter("@RemoteName",objExCall.RemoteName),
                new SqlParameter("@Port",objExCall.Port),
                new SqlParameter("@PortName",objExCall.PortName),
                new SqlParameter("@RStationOne",objExCall.Station1),
                new SqlParameter("@RStationTwo",objExCall.Station2),
                new SqlParameter("@RStationThreed",objExCall.Station3),
                new SqlParameter("@TrackId",objExCall.TrackId),
                new SqlParameter("@StartTime",objExCall.StartTime)
            };
            int result = Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_AddUnfinishCall", param));
            return result;
        }
        public int DeleteUnfinishCall(ExCall objExCall)
        {
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@IP",objExCall.IP),
                new SqlParameter("@Port",objExCall.Port),
                new SqlParameter("@StartTime",objExCall.StartTime)
            };
            return SQLHelper.UpdateByProcedure("usp_DelUnfinishCall", param);
        }
        /// <summary>
        /// 批量删除呼叫数据信息
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool DeleteUnfinishCallByTran(List<ExCall> list)
        {
            List<string> stringList = new List<string>();
            StringBuilder stringBuilder = new StringBuilder("delete from UnfinishCall ");
            stringBuilder.Append("where IP ='{0}' and Port ={1} and StartTime ='{2}'");
            foreach (ExCall item in list)
	        {
		        string sql = string.Format(stringBuilder.ToString(),item.IP,item.Port,item.StartTime.ToString());
                stringList.Add(sql);
	        }
            return SQLHelper.UpdateByTran(stringList);
        }
        public bool AddFinishCallByTran(List<ExCall> list)
        {
            List<string> stringList = new List<string>();
            StringBuilder stringBuilder = new StringBuilder("insert into FinishCall(IP,RemoteName,Port,PortName,RStationOne,");
            stringBuilder.Append("RStationTwo,RStationThreed,StartTime,FinishTime,AGVName,TrackId) ");
            stringBuilder.Append("values('{0}','{1}',{2},'{3}',{4},{5},{6},'{7}','{8}','{9}',{10})");
            foreach (ExCall item in list)
            {
                string sql = string.Format(stringBuilder.ToString(),item.IP,item.RemoteName,item.Port,item.PortName,item.Station1,
                                                        item.Station2,item.Station3,item.StartTime,item.FinishTime,item.AGVName,item.TrackId);
                stringList.Add(sql);
            }
            return SQLHelper.UpdateByTran(stringList);
        }
        /// <summary>
        /// 根据时间获取已完成的呼叫信息
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public List<ExCall> GetFinishCallByTime(DateTime dateTime)
        {
            /*查询当日完成的数据，数据库存储过程中代码*/
            SqlDataReader objReader = SQLHelper.GetReaderByProcedure("usp_CheckFinishCallByTime", null);
            List<ExCall> list = new List<ExCall>();
            while (objReader.Read())
            {
                list.Add(new ExCall()
                {
                    IP = objReader["IP"].ToString(),
                    RemoteName = objReader["RemoteName"].ToString(),
                    PortName = objReader["PortName"].ToString(),
                    Port = Convert.ToInt32(objReader["Port"]),
                    StartTime = Convert.ToDateTime(objReader["StartTime"]),
                    FinishTime=Convert.ToDateTime(objReader["FinishTime"]),
                    Station1 = Convert.ToInt32(objReader["RStationOne"]),
                    Station2 = Convert.ToInt32(objReader["RStationTwo"]),
                    Station3 = Convert.ToInt32(objReader["RStationThreed"]),
                    TrackId = Convert.ToInt32(objReader["TrackId"]),
                    TrackName = objReader["TrackName"].ToString(),
                    AGVName = objReader["AGVName"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 按时间删除已完成的信息
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        public int DeleteFinishCallByTime(string time)
        {
            string sql = "delete from FinishCall where FinishTime <='{0}'";
            sql = string.Format(sql, time);
            return SQLHelper.Update(sql);
        }
        public List<ExCall> GetRemoteName()
        {
            string sql = "select s.RemoteID,s.IP, s.RemoteName from ( select RemoteID,IP,RemoteName, row_number() over (partition by [RemoteName] order by [RemoteID]) as group_idx  from RemoteIO) s where s.group_idx = 1";
            SqlDataReader objReader = SQLHelper.GetReader(sql);
            List<ExCall> list = new List<ExCall>();
            while (objReader.Read())
            {
                list.Add(new ExCall()
                {
                    RemoteName = objReader["RemoteName"].ToString(),
                    RemoteID = Convert.ToInt32(objReader["RemoteId"]),
                    IP = objReader["IP"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
    }  
}
